<?php

include_once('ConnectionManager.php');

function selectAll() {
    $query = "SELECT * FROM CATEGORY ORDER BY CAT_ORDER ASC";
    ConnectionManager::getInstance()->setQuery($query);
    return ConnectionManager::getInstance()->query();
}

function countByName($keyword) {
    $select = "SELECT CAT_ID ";
    $from = "FROM CATEGORY ";
    $where = "WHERE CAT_NAME LIKE '%" . $keyword . "%'";

    ConnectionManager::getInstance()->setQuery($select . $from . $where);
    ConnectionManager::getInstance()->query();
    return ConnectionManager::getInstance()->getNumRows();
}

function countByOrder($order){
    $select = "SELECT CAT_ID ";
    $from = "FROM CATEGORY ";
    $where = "WHERE CAT_ORDER = " . $order;

    ConnectionManager::getInstance()->setQuery($select . $from . $where);
    return ConnectionManager::getInstance()->query();
}

function delete($id) {
    $sql = "DELETE FROM CATEGORY WHERE CAT_ID = " . $id;
    ConnectionManager::getInstance()->setQuery($sql);
    return ConnectionManager::getInstance()->query();
}

function loadCategoryByID($cateID) {
    $query = "SELECT * FROM CATEGORY WHERE CAT_ID = $cateID";
    ConnectionManager::getInstance()->setQuery($query);
    $result = ConnectionManager::getInstance()->query();
    return mysql_fetch_array($result);
}

function countTotalCATEGORY() {
    $query = "SELECT CAT_ID FROM CATEGORY";
    ConnectionManager::getInstance()->setQuery($query);
    ConnectionManager::getInstance()->query();
    return ConnectionManager::getInstance()->getNumRows();
}

function update($catID, $name, $order) {
    $sql = "UPDATE CATEGORY 
                SET CAT_NAME = '$name', CAT_ORDER = $order 
                WHERE CAT_ID = $catID";
    ConnectionManager::getInstance()->setQuery($sql);
    return ConnectionManager::getInstance()->query();
}

function insert($name, $order) {
    $sql = "INSERT INTO CATEGORY (CAT_NAME, CAT_ORDER) 
                VALUES ('$name', $order)";
    ConnectionManager::getInstance()->setQuery($sql);
    return ConnectionManager::getInstance()->query();
}

function findBy($keyword, $start_position, $row_per_page) {
    $select = "SELECT C.CAT_ID, C.CAT_NAME, C.CAT_ORDER, (SELECT COUNT(*) FROM DISH D WHERE D.CAT_ID = C.CAT_ID) AS NUMDISH
	           FROM CATEGORY C ";
    $where = "";
    if (isset($keyword)) {
        $where .= "WHERE CAT_NAME LIKE '%$keyword%'";
    }

    $where .= " LIMIT " . $start_position . ", " . $row_per_page;
    ConnectionManager::getInstance()->setQuery($select . $where);
    return ConnectionManager::getInstance()->query();
}

function findCategoryByOrder($order, $keyword){
	$stringArr = preg_split("/ /", htmlspecialchars(str_replace("d","đ",htmlspecialchars($keyword, ENT_QUOTES)), ENT_QUOTES));	
	$seachString = "";
	foreach($stringArr as $each){
		$seachString .= "$each"."%";
	}
	$sql = "SELECT CAT_ID, CAT_NAME 
			FROM CATEGORY 
			WHERE CAT_ORDER = $order AND CAT_ID IN (SELECT DISTINCT CAT_ID
													FROM DISH
													WHERE DISH_NAME LIKE '%".$seachString."')";
	ConnectionManager::getInstance()->setQuery($sql);
    return ConnectionManager::getInstance()->query();
}

function findCategorySortByOrder(){
	$sql = "SELECT * FROM CATEGORY ORDER BY ORDER ASC";
	ConnectionManager::getInstance()->setQuery($sql);
    return ConnectionManager::getInstance()->query();
}

function findCategoryByDish($keyword){
	$sql = "SELECT CAT_NAME, CAT_ID 
	        FROM CATEGORY 
			WHERE CAT_ID in (SELECT DISTINCT CAT_ID 
			                 FROM DISH 
							 WHERE DISH_NAME LIKE '%".htmlspecialchars($keyword, ENT_QUOTES)."%') AND CAT_ID <> (SELECT CAT_ID FROM CATEGORY WHERE CAT_ORDER = 1)
		    ORDER BY CAT_ORDER ASC";
	ConnectionManager::getInstance()->setQuery($sql);
    return ConnectionManager::getInstance()->query();
}

?>